package com.example.booksystem.dao;

import com.example.booksystem.bean.Book;
import com.example.booksystem.bean.Type;
import com.example.booksystem.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    //通过类型查询
    public List<Book> getBooksByTypeId(long id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Book> books = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from book where typeId=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                books.add(new Book(resultSet.getLong(1),resultSet.getLong(2),resultSet.getString(3),resultSet.getFloat(4),resultSet.getString(5),resultSet.getString(6),resultSet.getString(7),resultSet.getString(8),resultSet.getLong(9),resultSet.getString(10)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return books;
    }
    //通过页码查询
    public List<Book> getByPage(int page,int size){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int offset = (page-1)*size;
        ArrayList<Book> books = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from book limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,offset);
            preparedStatement.setInt(2,size);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                books.add(new Book(resultSet.getLong(1),resultSet.getLong(2),resultSet.getString(3),resultSet.getFloat(4),resultSet.getString(5),resultSet.getString(6),resultSet.getString(7),resultSet.getString(8),resultSet.getLong(9),resultSet.getString(10)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return books;
    }
    //通过ID查询
    public Book getById(long id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Book book = new Book();

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from book where id=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                book  = new Book(resultSet.getLong(1), resultSet.getLong(2), resultSet.getString(3), resultSet.getFloat(4), resultSet.getString(5), resultSet.getString(6), resultSet.getString(7), resultSet.getString(8), resultSet.getLong(9), resultSet.getString(10));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return book;
    }
    //增加
    public int add(long typeId,String name,float price,String desc,String pic,String publish,String author,long stock,String address){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "insert into book (typeId,`name`,price,`desc`,pic,publish,author,stock,address) values(?,?,?,?,?,?,?,?,?);";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,typeId);
            preparedStatement.setString(2,name);
            preparedStatement.setFloat(3,price);
            preparedStatement.setString(4,desc);
            preparedStatement.setString(5,pic);
            preparedStatement.setString(6,publish);
            preparedStatement.setString(7,author);
            preparedStatement.setLong(8,stock);
            preparedStatement.setString(9,address);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }
    //删除
    public int remove(long id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "delete from book where id=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }
    //修改
    public int modify(long typeId,String name,float price,String desc,String pic,String publish,String author,long stock,String address,long id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "update book set typeId=?,`name`=?,price=?,`desc`=?,pic=?,publish=?,author=?,stock=?,address=? where id=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,typeId);
            preparedStatement.setString(2,name);
            preparedStatement.setFloat(3,price);
            preparedStatement.setString(4,desc);
            preparedStatement.setString(5,pic);
            preparedStatement.setString(6,publish);
            preparedStatement.setString(7,author);
            preparedStatement.setLong(8,stock);
            preparedStatement.setString(9,address);
            preparedStatement.setLong(10,id);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }

    //书籍类型数量
    public int getCount(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "select count(id) from book";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                result = (int)(resultSet.getLong(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }

    public Book getByName(String bookName){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Book book = new Book();

        try {
            connection = DBUtil.getConnection();
            String sql="select * from book where name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,bookName);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                book  = new Book(resultSet.getLong(1), resultSet.getLong(2), resultSet.getString(3), resultSet.getFloat(4), resultSet.getString(5), resultSet.getString(6), resultSet.getString(7), resultSet.getString(8), resultSet.getLong(9), resultSet.getString(10));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return book;
    }

    public void modify(long bookId, int i) {

    }
}
